package com.blog.Bard.common.dao;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.internal.SessionImpl;
import org.hibernate.query.NativeQuery;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

import com.blog.Bard.common.util.MyMap;
import com.blog.Bard.common.util.Pager;

/**
 * dao层基类(JPA增强类)，扩展JPA方法
 * 可以通过自定义sql，动态参数查询结果，弥补原来JPA语法定义，需要固定的SQL和参数传入
 */
public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

	public final EntityManager entityManager;
	
	public BaseRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager) {
		super(entityInformation, entityManager);
		this.entityManager = entityManager;
	}
	
	@Override
	public T queryForObject(Class<T> clazz, String sql, Object... args) throws Exception {
		List<T> list = this.queryForList(clazz, sql, args);
		if(list!=null && !list.isEmpty()) {
			return list.get(0);
		}else {
			return null;
		}
	}
	
	@SuppressWarnings({"unchecked" })
	@Override
	public List<T> queryForList(Class<T> clazz, String sql, Object... args) throws Exception {
		Session session = (Session)entityManager.getDelegate();
		NativeQuery<T> query = session.createSQLQuery(sql);
		query.addEntity(clazz);
		int i = 0;
		for (Object arg : args) {
			query.setParameter(++i, arg);
		}
		return query.list();
	}
	
	/**
	 * 有问题，弃用
	 */
	@Override
	public Page<T> queryForPage(Class<T> clazz, Pageable pageable, String sql, Object... args) throws Exception {
		List<T> list = this.queryForList(clazz, sql, args);
		Page<T> pages = new PageImpl<>(list, pageable, list.size());
		return pages;
	}
	
	@Override
	@SuppressWarnings({ "deprecation", "rawtypes", "unchecked" })
	public List<Map<String, Object>> queryForList(String sql, Object... args) throws Exception {
		Session session = (Session) entityManager.getDelegate();
		NativeQuery query = session.createSQLQuery(sql);
		query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		int i = 0;
		for (Object arg : args) {
			query.setParameter(++i, arg);
		}
		return query.list();
	}
	
	@Override
	public Map<String, Object> queryForMap(String sql, Object... args) throws Exception {
		List<Map<String, Object>> list = this.queryForList(sql, args);
		if(list!=null && !list.isEmpty()) {
			return list.get(0);
		}
		return null;
	}
 
	/**
	 * 有问题，弃用
	 */
	@Override
	public Page<Map<String, Object>> queryForPage(Pageable pageable, String sql, Object... args) throws Exception {
		List<Map<String, Object>> list = this.queryForList(sql, args);
		Page<Map<String, Object>> pages = new PageImpl<>(list, pageable, list.size());
		return pages;
	}
	
	@Override
	public Pager getPagerBySql(final String sql, final int pageNo, final int pagesize,
			final Object... args) {
		try{
			int ipos = sql.toLowerCase().indexOf("order by ");
			String sql2 = sql;
			if (ipos != -1) {
				sql2 = sql2.substring(0, ipos);
			}
			java.sql.Connection connection = entityManager.unwrap(SessionImpl.class).connection();
			String countSql = "select count(0) from (" + sql2+ ") as tmp_count";
			PreparedStatement stmt = connection.prepareStatement(countSql, 
					ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (args != null)
				for (int i = 0; i < args.length; i++) {
					stmt.setObject(i+1, args[i]);
				}
			ResultSet rs = stmt.executeQuery();
			int count = 0;
			if (rs.next()) {
				count = rs.getInt(1);
			}  
			rs.close();
			stmt.close();  

			if(count==0){
				//处理在SQL中用链接服务器没有数据时用top报错
				return new Pager(1,0,pagesize,new ArrayList<MyMap>(0));
			}else{
				int totalPage = (count-1)/pagesize +1;
				int currentPage= 0;
				if(pageNo > totalPage)
					currentPage = totalPage;
				else
					currentPage = pageNo;
				stmt = connection.prepareStatement(sql, 
						ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
				if (args != null)
					for (int i = 0; i < args.length; i++) {
						stmt.setObject(i+1, args[i]);
					}
				rs = stmt.executeQuery();
				List<MyMap> lallrecord = resultSetToList(rs,currentPage,pagesize);
				Pager pager = new Pager(currentPage, count, pagesize,
						lallrecord);
				rs.close();
				stmt.close();
				return pager;
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		return null;
	}
	
	private List<MyMap> resultSetToList(ResultSet rs,final int pageNo, final int pagesize) {
		List<MyMap> list = new  ArrayList<MyMap>();
		ResultSetMetaData rsm = null;
		String fieldname;
		try {
			rsm = rs.getMetaData();
			rs.beforeFirst();
			for(int i = 1;i<=(pageNo - 1) * pagesize;i++){
				if(!rs.next()){
					break;
				}
			}
			while(rs.next() && list.size()<pagesize){
				MyMap map = new MyMap();
				for (int i = 1; i <= rsm.getColumnCount(); i++) {
					fieldname = rsm.getColumnLabel(i);
					map.put(fieldname, rs.getObject(i));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
}
